open Printf
open Common
open Store
open ExtLib
open Dispose

let show_row row =
	for i = 0 to (Array.length row) - 2 do
		printf "%s | " (Sqlite3.Data.to_string row.(i))
	done;
	printf "%s\n" (Sqlite3.Data.to_string row.((Array.length row) - 1))

let create_empty_db h =
	let sql = Resources.read_file "init_tables.sql" in
		using
			(create_multiple_stmt h sql)
			(fun l -> List.iter execute_non_query l#value);
		h

let fill_db_with_data h =
	let sql = Resources.read_file "test_fill.sql" in
		using
			(create_multiple_stmt h sql)
			(fun l -> List.iter execute_non_query l#value);
		h
			
let read_and_print_all h =
	let sql = Resources.read_file "load_all.sql" in
		using
			(new Store.squery (create_single_stmt h sql))
			(fun q -> List.iter show_row (q#execute_query_dataset()));
		h

let add_new h title year =
	let sql = "insert into films (title, year) values (:001, :002); select last_insert_rowid();" in
		using
			(create_multiple_stmt h sql)
			(fun l -> match l#value with a :: c :: [] ->
				 set_parameters a [|Sqlite3.Data.TEXT(title); Sqlite3.Data.INT(Int64.of_int year)|];
				 execute_non_query a;
				 execute_scalar c |> Option.get |> Sqlite3.Data.to_string)

let count h ?(whereStr="") ?(whereParams=[||]) tableName =
	let sql =
		if (String.length whereStr) = 0 then
			sprintf "select count() from %s;" tableName
		else
			sprintf "select count() from %s where %s;" tableName whereStr
	in
		using
			(create_single_stmt h sql)
			(fun stmt ->
				 if (Array.length whereParams) > 0 then
					 set_parameters stmt whereParams |> ignore;
				 match execute_scalar stmt |> Option.get with
					 | Sqlite3.Data.INT(i) -> Int64.to_int i
					 | _ -> assert false)
			
let _ =
	if Sys.file_exists "testing" then Sys.remove "testing";
	try
		do_finally_p2
			(fun h -> 
				 h |> create_empty_db |> fill_db_with_data |> read_and_print_all |> ignore;
				 add_new h "Some name" 2000 |> printf "new added rowid %s\n";
				 count h
					 ~whereStr:"year > :001 and year < :002"
					 ~whereParams:[|Sqlite3.Data.INT(1990L);Sqlite3.Data.INT(2000L)|] "films" |> printf "Count = %d"
			)
			(fun h -> if not (Sqlite3.db_close h) then failwith (Sqlite3.errmsg h))
			(Sqlite3.db_open "testing");
	with exn ->
		printf "Error: %s\n" (Printexc.to_string exn)
